<?php

namespace App\Http\Controllers\VehicleAccounting;

use App\Http\Controllers\Controller;
use App\Models\Accounting\AccountingBusNote;
use App\Models\Accounting\AccountingImportData;
use App\Models\Accounting\AccountingRate;
use App\Models\Accounting\AccountingReport;
use App\Models\Accounting\AccountingReportFields;
use App\Models\Charge\Charge;
use App\Models\Mileage\Mileage;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Exception;

class VehicleAccountingController extends Controller
{
    /**
     * 单车核算
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function vehicleAccounting(Request $request)
    {
        $params = $request->all();

        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-01');
        }
        $result = $this->getVehicleAccounting($params);
        return response()->formatJson(['result' => $result, 'date' => $params['date'],]);
    }

    public function getVehicleAccounting($params)
    {
        $endDate = date('Y-m-t', strtotime($params['date']));
        $rateRecord = AccountingRate::query()
            ->where('start', '>=', $params['date'])
            ->where('end', '<=', $params['date'])
            ->first();
        $rate = $rateRecord ? $rateRecord->rate : 0;

        $resultIncome = $this->vehicleTransData($params)->get()->groupBy('车牌号')->all();
        $result = [];
        foreach ($resultIncome as $k => $v) {
            $line = implode(',', array_unique($v->pluck('线路')->toArray()));
            $cash = $v->sum('现金收入');
            $card = $v->sum('一卡通收入');
            $post = $v->sum('邮储收入');
            $fixed = $v->sum('包车收入');
            $result[$k] = [
                'line'              => $line,
                'license_plate_num' => $k,
                'business_income'   => number_format(($cash + $card + $post + $fixed), 2, '.', ''),
            ];
        }

        // 备注
        $busNote = AccountingBusNote::query()->where('month', $params['date'])->get()->toArray();
        foreach ($busNote as $k => $v) {
            foreach ($result as $kk => &$vv) {
                if ((int) $v['license_plate_num'] === $kk) {
                    $vv['note'] = $v['note'];
                }
            }
        }

        // 逸华加气

        $resultYh = DB::table('v_gas')
            ->where('类型', '>', 0)
            ->whereBetween('日期', [$params['date'].' 00:00:00', $endDate.' 23:59:59'])
            ->get()
            ->groupBy('车牌号')
            ->all();
        if ($resultYh) {
            foreach ($resultYh as $k => $v) {
                foreach ($result as $kk => &$vv) {
                    if ($k === $kk) {
                        $vv['m_air_yihua'] = number_format($v->sum('金额'), 2, '.', '');
                    }
                }
            }
        }

        // 顺康加气
        $month = $params['date'];
        $resultSk = DB::table('v_gas_sk')
            ->whereBetween('日期', [date('Y-m-t 08:00:00', strtotime("$month -1 month")), $endDate.' 07:59:59'])
            ->get()
            ->groupBy('车牌号')
            ->all();
        if ($resultSk) {
            foreach ($resultSk as $k => $v) {
                foreach ($result as $kk => &$vv) {
                    if ($k === $kk) {
                        $vv['monthly_cost'] = number_format($v->sum('金额'), 2, '.', '');
                    }
                }
            }
        }

        // 里程记录
        $millage = Mileage::query()->whereBetween('date', [$params['date'], $endDate])->get();

        // 获取所有数据
        $dataQuery = AccountingImportData::query();
        if (isset($params['license_plate_num'])) {
            $dataQuery->where('license_plate_num', 'like', '%'.$params['license_plate_num'].'%');
        }

        if (isset($params['line']) && $params['line'] !== '-1') {
            $dataQuery->where('line', '=', $params['line']);
        }

        $data = $dataQuery->where('month', $params['date'])->get()->groupBy('license_plate_num')->toArray();

        // 获取数据对应关系
        $reportId = AccountingReport::query()->where('report_name', '单车核算统计')->first()->id;
        $fields = [];
        $parent = AccountingReportFields::query()
            ->where('report_id', $reportId)
            ->where('parent_id', 0)
            ->orderBy('sort')
            ->get()
            ->toArray();

        foreach ($parent as $k => $v) {
            $son = AccountingReportFields::query()
                ->where('report_id', $reportId)
                ->where('parent_id', $v['id'])
                ->orderBy('sort')
                ->get()
                ->toArray();

            if (! $son) {
                $fields[] = $v;
            }
            foreach ($son as $key => $value) {
                $value['parent'] = $v['header_name'];
                $fields[] = $value;
            }
        }

        // 匹配数据
        $resultData = [];
        $ignoreKey = ['车牌号', '线路', '备注', '类别'];
        foreach ($data as $name => $v) {
            $part = [];
            foreach ($fields as $key => $item) {
                if ($item['header_name'] === '车牌号') {
                    $part[$item['header_name']] = $name;
                } else {
                    if ($item['table_name']) {
                        $sign = collect($v)->where('table_name', $item['table_name'])->first();
                        if ($sign) {
                            $signData = $sign['data'];
                            if (in_array($item['column_name'], $ignoreKey, true)) {
                                $part[$item['header_name']] = json_decode($signData, true)[$item['column_name']];
                            } else {
                                $signDataArr = json_decode($signData, true);
                                $part[$item['header_name']] = isset($signDataArr[$item['column_name']]) ?
                                    number_format($signDataArr[$item['column_name']], 2, '.', '') :
                                    0;
                            }
                        } else {
                            $part[$item['header_name']] = '';
                        }
                    } else {
                        $part[$item['header_name']] = '';
                    }
                }
            }
            $resultData[$name] = $part;
        }

        //充电记录
        $start_date = date('Y-m-d 00:00:00', strtotime($params['date']));
        $end_date = date('Y-m-d 23:59:59', strtotime($endDate));
        $chargeQuery = Charge::query()
            ->whereBetween('charging_end_at', [$start_date, $end_date])
            ->get()
            ->toArray();
        foreach ($result as $key => $v) {
            // 趟次
            $licensePlateNumCount = collect($millage)->where('license_plate_num', '陕G'.$v['license_plate_num']);
            if ($licensePlateNumCount) {
                $planCount = $licensePlateNumCount->sum('plan_times');
                $dropCount = $licensePlateNumCount->sum('drop_off');
                $result[$key]['num'] = $planCount - $dropCount;
                $result[$key]['monthly_km'] = number_format($licensePlateNumCount->sum('gps_mileage'), 2, '.', '');
            } else {
                $result[$key]['num'] = 0;
                $result[$key]['monthly_km'] = 0;
            }
            // 含税实际收入
            $result[$key]['business_income'] = $v['business_income'];
            // 入账主营收入
            $result[$key]['bookedIncome'] = $v['business_income'] * (1 - ($rate / 100));
            //充电记录
            $chargeData = collect($chargeQuery)
                ->where('license_plate_num', '陕G'.$v['license_plate_num'])
                ->all();
            $totalTradingPower = 0;
            if ($chargeData) {
                $totalTradingPower = number_format(collect($chargeData)->sum('trading_power'), 2, '.', '');
            }
            $result[$key]['total_trading_power'] = $totalTradingPower;
        }

        foreach ($result as $kkk => $vvv) {
            foreach ($resultData as $kkkk => $vvvv) {
                if ($vvvv['车牌号'] === (int) $vvv['license_plate_num']) {
                    $premium = $vvvv['保险费'] && $vvvv['保险费'] !== null ? $vvvv['保险费'] : 0;
                    $cost = $vvvv['费用'] && $vvvv['费用'] !== null ? $vvvv['费用'] : 0;
                    $salary = $vvvv['工资'] && $vvvv['工资'] !== null ? $vvvv['工资'] : 0;
                    $repair = $vvvv['修理费'] && $vvvv['修理费'] !== null ? $vvvv['修理费'] : 0;
                    $tire = $vvvv['轮胎'] && $vvvv['轮胎'] !== null ? $vvvv['轮胎'] : 0;
                    $depreciation = $vvvv['折旧'] && $vvvv['折旧'] !== null ? $vvvv['折旧'] : 0;
                    $check = $vvvv['洗车、例检'] && $vvvv['洗车、例检'] !== null ? $vvvv['洗车、例检'] : 0;
                    $insurance = $vvvv['五险'] && $vvvv['五险'] !== null ? $vvvv['五险'] : 0;
                    $other = $vvvv['其他'] && $vvvv['其他'] !== null ? $vvvv['其他'] : 0;
                    $taxes = $vvvv['营业税金及附加'] && $vvvv['营业税金及附加'] !== null ? $vvvv['营业税金及附加'] : 0;

                    $result[$kkk]['premium'] = $premium;
                    $result[$kkk]['cost'] = $cost;
                    $result[$kkk]['salary'] = $salary;
                    $result[$kkk]['repair'] = $repair;
                    $result[$kkk]['tire'] = $tire;
                    $result[$kkk]['depreciation'] = $depreciation;
                    $result[$kkk]['check'] = $check;
                    $result[$kkk]['insurance'] = $insurance;
                    $result[$kkk]['other'] = $other;
                    $result[$kkk]['taxes'] = $taxes;
                    $count = $premium + $cost + $repair + $tire + $salary + $check + $insurance + $other + $taxes + $depreciation;
                    $result[$kkk]['importData'] = $count ? number_format($count, 2, '.', '') : 0;
                }
            }
        }

        foreach ($result as $kkk => $vvv) {
            $importData = array_key_exists('importData', $vvv) ? $vvv['importData'] : 0;
            $salary = array_key_exists('salary', $vvv) ? $vvv['salary'] : 0;
            $monthlyCost = array_key_exists('monthly_cost', $vvv) ? $vvv['monthly_cost'] : 0;
            $mAirYihua = array_key_exists('m_air_yihua', $vvv) ? $vvv['m_air_yihua'] : 0;

            $subtotal = $salary + $monthlyCost + $mAirYihua + $importData;
            $result[$kkk]['subtotal'] = $subtotal ? number_format($subtotal, 2, '.', '') : 0;

            $taxes = array_key_exists('taxes', $vvv) ? $vvv['taxes'] : 0;
            $profits = $vvv['business_income'] - $taxes - $subtotal;
            $result[$kkk]['profits'] = $profits ? number_format($profits, 2, '.', '') : 0;
        }

        $sortResult = collect($result)->sortByDesc('profits')->toArray();

        $sortResult = $this->rebuildKeys($sortResult);

        $result = $this->rebuildKeys($result);
        foreach ($result as $k => $v) {
            foreach ($sortResult as $sort => $vv) {
                if ($vv['license_plate_num'] === $v['license_plate_num']) {
                    $result[$k]['rank'] = $sort + 1;
                }
            }
        }

        return $result;
    }

    public function vehicleTransData($params)
    {
        $query = DB::table('v_income');

        $query->whereBetween('日期', [$params['date'], date('Y-m-t', strtotime($params['date']))]);

        if (isset($params['license_plate_num'])) {
            $query->where('车牌号', 'like', '%'.$params['license_plate_num'].'%');
        }

        if (isset($params['line']) && $params['line'] !== '-1') {
            $query->where('线路', $params['line']);
        }

        return $query->orderBy('车牌号');
    }

    public function rebuildKeys($array)
    {
        if (! is_array($array)) {
            return $array;
        }
        $keys = implode('', array_keys($array));
        if (is_numeric($keys)) {
            $array = array_values($array);
        }
        $array = array_map([$this, 'rebuildKeys'], $array);

        return $array;
    }

    public function updateBusNote(Request $request)
    {
        $params = $request->all();
        $data = collect($params['updateRecords'])->pluck('note', 'license_plate_num')->toArray();
        $date = date('Y-m-d', strtotime($params['date']));
        foreach ($data as $license_plate_num => $note) {
            $result = AccountingBusNote::query()->updateOrCreate(
                [
                    'month'             => $date,
                    'license_plate_num' => $license_plate_num,
                ],
                [
                    'note' => $note,
                ]
            );
        }
        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 导出单车核算
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws Exception
     */
    public function exportVehicleAccounting(Request $request)
    {
        $params = $request->all();

        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-01');
        }
        $data = $this->getVehicleAccounting($params);

        $date = $params['date'];
        if ($date === '1970-01-01') {
            $date = date('Y-m-01');
        }
        $endDate = date('Y-m-t', strtotime($date));
        $title = '安康市公共交通责任有限公司单车核算表('.$date.'--'.$endDate.')';
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        $spreadsheet->createSheet();
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', $title)
            ->setCellValue('G2', '成本项目')
            ->setCellValue('A2', '车号')->setCellValue('B2', '线路')->setCellValue('C2', '车公里')
            ->setCellValue('D2', '趟次')->setCellValue('E2', '含税实际主营收入')
            ->setCellValue('F2', '入账主营收入')
            ->setCellValue('S3', '小 计')
            ->setCellValue('G3', '工资')->setCellValue('H3', '充电')->setCellValue('I3', '气耗')
            ->setCellValue('J3', '逸华加气')->setCellValue('K3', '修理费')
            ->setCellValue('L3', '轮胎')->setCellValue('M3', '保险费')
            ->setCellValue('N3', '费用')->setCellValue('O3', '折旧')->setCellValue('P3', '洗车、例检')
            ->setCellValue('Q3', '五险')->setCellValue('R3', '其他')
            ->setCellValue('T2', '营业税金及附加')->setCellValue('U2', '实际主营利润')
            ->setCellValue('V2', '盈利亏损排列')->setCellValue('W2', '备注');

        $num = 4;
        for ($i = 0, $iMax = count($data); $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $data[$i]['license_plate_num']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, preg_replace('/\b0\b/', '停运', $data[$i]['line']));
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $data[$i]['monthly_km'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $data[$i]['num'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $data[$i]['business_income'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $data[$i]['bookedIncome'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num,
                array_key_exists('salary', $data[$i]) ? $data[$i]['salary'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num,
                array_key_exists('total_trading_power', $data[$i]) ? $data[$i]['total_trading_power'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num,
                array_key_exists('monthly_cost', $data[$i]) ? $data[$i]['monthly_cost'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num,
                array_key_exists('m_air_yihua', $data[$i]) ? $data[$i]['m_air_yihua'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num,
                array_key_exists('repair', $data[$i]) ? $data[$i]['repair'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num,
                array_key_exists('tire', $data[$i]) ? $data[$i]['tire'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num,
                array_key_exists('premium', $data[$i]) ? $data[$i]['premium'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num,
                array_key_exists('cost', $data[$i]) ? $data[$i]['cost'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('O'.$num,
                array_key_exists('depreciation', $data[$i]) ? $data[$i]['depreciation'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('P'.$num,
                array_key_exists('check', $data[$i]) ? $data[$i]['check'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('Q'.$num,
                array_key_exists('insurance', $data[$i]) ? $data[$i]['insurance'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('R'.$num,
                array_key_exists('other', $data[$i]) ? $data[$i]['other'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('S'.$num,
                array_key_exists('subtotal', $data[$i]) ? $data[$i]['subtotal'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('T'.$num,
                array_key_exists('taxes', $data[$i]) ? $data[$i]['taxes'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('U'.$num,
                array_key_exists('profits', $data[$i]) ? $data[$i]['profits'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('V'.$num,
                array_key_exists('rank', $data[$i]) ? $data[$i]['rank'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('W'.$num,
                array_key_exists('note', $data[$i]) ? $data[$i]['note'] : '');
            $num++;
        }
        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + 1), '总合计:');
        //格式化数据为千分位
        $spreadsheet->getActiveSheet()->getStyle('C4:V'.($num + 1))
            ->getNumberFormat()->setFormatCode('#,##0.00');
        //写入计算公式
        $array = ['C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V'];
        foreach ($array as $value) {
            $spreadsheet->getActiveSheet()->setCellValue($value.($num + 1), '=SUM('.$value.'4:'.$value.$num.')');
            $spreadsheet->getActiveSheet()->getCell($value.($num + 1))->getCalculatedValue();
        }

        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:W1')->mergeCells('G2:S2')
            ->mergeCells('A2:A3')->mergeCells('B2:B3')->mergeCells('C2:C3')
            ->mergeCells('D2:D3')->mergeCells('E2:E3')->mergeCells('F2:F3')
            ->mergeCells('T2:T3')->mergeCells('U2:U3')->mergeCells('V2:V3')->mergeCells('W2:W3')
            ->mergeCells('A'.$num.':V'.$num)->mergeCells('A'.($num + 1).':B'.($num + 1));

        //  设置宽度
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12); // 默认列宽设置为12
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical'   => Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:W3')->applyFromArray($numberStyleCenter)
            ->getAlignment()->setWrapText(true);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A2:W3')->getFont()->setBold(true);
        //加边框
        $borderStyleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color'       => ['argb' => '000000000'],
                ],
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:W'.($num + 1))->applyFromArray($borderStyleArray);
        // 重命名 worksheet
        $c = '单车核算'.$date.'--'.$endDate;
        $spreadsheet->getActiveSheet()->setTitle($c);

        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$c.'.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
